﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Drawing.Imaging
Public Class Frm_Gestion_Personnels
    Public num As Integer = 0
    Public modif As Integer = 0
    Public Sub vider()

        txt_nom.Clear()
        txt_prenom.Clear()
        txt_cin.Clear()
        txt_adresse.Clear()
        txt_mail.Clear()

        txt_salair.Clear()
        txt_salair.Text = 0
        txt_portabl.Clear()
        txt_remarq.Clear()
        Check_actif.Checked = False
        If Comb_Type_Travail.Items.Count > 0 Then
            Comb_Type_Travail.SelectedIndex = 0
        End If
        If comb_ville.Items.Count > 0 Then
            comb_ville.SelectedIndex = 0
        End If
        Pic_photo.Image = My.Resources.photo
        txt_nom.Select()
        modif = 0
    End Sub

    Public Sub Afficher()
        Try
            List_tous.Items.Clear()
            Cmd = New SqlCommand("Select * From Personnels", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                Dim Line As ListViewItem = New ListViewItem(New String() {Dr("Nom"), Dr("Prenom"), Dr("Adresse"), Dr("categorie"), Dr("Cin"), Dr("Email"), Dr("Ville"), Dr("Id_Personnels")})
                List_tous.Items.Add(Line)
            Loop
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Public Sub com_ville()
        Try
            comb_ville.Items.Clear()
            Cmd = New SqlCommand("Select * From Comb_Ville", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                comb_ville.Items.Add(Dr("Ville"))
            Loop
            Dr.Close()
            cnClose()
            comb_ville.SelectedIndex = 0
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Public Sub Comb_catégori()
        Try
            Comb_Type_Travail.Items.Clear()
            Cmd = New SqlCommand("Select * From Comb_Categ_Personnels", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                Comb_Type_Travail.Items.Add(Dr("Categorie"))
            Loop
            Dr.Close()
            cnClose()
            If Comb_Type_Travail.Items.Count > 0 Then
                Comb_Type_Travail.SelectedIndex = 0
            End If

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub btn_parcour_ville_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_parcour_ville.Click
        Me.Enabled = False
        Frm_ville.Show()
    End Sub

    Private Sub btn_parc_travail_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_parc_travail.Click
        Frm_Type_Travail.Show()
    End Sub

    Private Sub btn_recher_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_recher.Click
        RechPersonl = "Gestion_Personnels"
        Frm_Recherche_Personnels.Show()
    End Sub

    Private Sub Frm_Gestion_Personnels_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Afficher()
        Comb_catégori()
        com_ville()
        txt_nom.Select()
        txt_salair.Text = 0
    End Sub

    Private Sub btn_nouv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nouv.Click
        vider()
    End Sub

    Private Sub btn_parcour_photo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_parcour_photo.Click
        With OpenF
            .FileName = ""
            .Title = "Ovrir la Photo"
            .RestoreDirectory = True
            .InitialDirectory = "C:\"
            .Filter = "JPEG Files (*.JPG)|*.JPG"
            .FilterIndex = 1
        End With
        If OpenF.ShowDialog = Windows.Forms.DialogResult.OK Then
            Pic_photo.Image = Image.FromFile(OpenF.FileName)

        End If
    End Sub

    Private Sub btn_supp_phot_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp_phot.Click
        Pic_photo.Image = My.Resources.photo
    End Sub

    Private Sub btn_ajouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ajouter.Click
        If (txt_nom.Text <> "" And txt_prenom.Text <> "") Then
            If MsgBox("Vous Voulez Vraiment Ajouter  ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Ajouter ") = MsgBoxResult.Yes Then
                Try
                    Cmd = New SqlCommand("insert into Personnels (Nom,Prenom,Cin,Adresse,Email,Categorie,Ville,Actif,Date_emploi,Salaire,Remarque,Photo)" & _
                    "values (@nom,@pren,@Cin,@adres,@email,@categ,@vill,@Actif,@Date_emploi,@Salaire,@remarq,@img)", Con)

                    Cmd.Parameters.Add("@nom", SqlDbType.VarChar).Value = txt_nom.Text
                    Cmd.Parameters.Add("@pren", SqlDbType.VarChar).Value = txt_prenom.Text
                    Cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = txt_cin.Text
                    Cmd.Parameters.Add("@categ", SqlDbType.VarChar).Value = Comb_Type_Travail.SelectedItem
                    Cmd.Parameters.Add("@adres", SqlDbType.VarChar).Value = txt_adresse.Text
                    Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = txt_mail.Text
                    If Check_actif.Checked = True Then
                        Cmd.Parameters.Add("@Actif", SqlDbType.VarChar).Value = "Actif"
                    Else
                        Cmd.Parameters.Add("@Actif", SqlDbType.VarChar).Value = ""
                    End If
                    Cmd.Parameters.Add("@vill", SqlDbType.VarChar).Value = comb_ville.SelectedItem
                    Cmd.Parameters.Add("@Date_emploi", SqlDbType.Date).Value = Date_emploi.Value.Date
                    Cmd.Parameters.Add("@Salaire", SqlDbType.Float).Value = CDbl(txt_salair.Text)
                    Cmd.Parameters.Add("@remarq", SqlDbType.Text).Value = txt_remarq.Text

                    'اضافة الصورة


                    Dim ms As MemoryStream = New MemoryStream()
                    Pic_photo.Image.Save(ms, ImageFormat.Jpeg)
                    Dim bytBLOBData(ms.Length - 1) As Byte
                    ms.Position = 0
                    ms.Read(bytBLOBData, 0, ms.Length)
                    Dim prm As New SqlParameter("@img", SqlDbType.VarBinary, _
                    bytBLOBData.Length, ParameterDirection.Input, False, _
                    0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
                    Cmd.Parameters.Add(prm)

                    open()
                    Cmd.ExecuteNonQuery()
                    cnClose()
                    ' num استخراج
                    Try
                        Cmd = New SqlCommand("Select * From Personnels", Con)
                        open()
                        Dr = Cmd.ExecuteReader()
                        If Dr.HasRows Then
                            Do While Dr.Read()
                                num = Dr("Id_Personnels")
                            Loop
                        End If

                        Dr.Close()
                        cnClose()

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try

                    'الهاتف

                    Cmd = New SqlCommand("insert into Personnels_Tele values (@numM,@tele)", Con)
                    Cmd.Parameters.Add("@numM", SqlDbType.Int).Value = num
                    Cmd.Parameters.Add("@tele", SqlDbType.VarChar).Value = txt_portabl.Text


                    open()
                    Cmd.ExecuteNonQuery()
                    cnClose()


                    MsgBox("Ajouté Avec Succes", MsgBoxStyle.Information, "Réussi !")
                    vider()
                    Afficher()

                Catch ex As Exception

                    MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                    cnClose()
                End Try
            End If

        Else
            MsgBox("Entrez les Données", MsgBoxStyle.Critical, "Erreur !")
        End If
    End Sub

    Private Sub btn_supp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp.Click


        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(7).Text)
                Next
                If MsgBox("Voulez Vous Vraiment Supprimer ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Supprission !") = MsgBoxResult.Yes Then
                    Try
                        Cmd = New SqlCommand("Delete From Personnels_Tele Where Id_Personnels =" & n, Con)
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                    Try
                        Cmd = New SqlCommand("Delete From Personnels Where Id_Personnels =" & n, Con)
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()
                        Afficher()
                        vider()
                        modif = 0
                        MsgBox("Suppresion effectué !", MsgBoxStyle.Information, "Terminer")
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                End If
            Else
                MsgBox("Vous n'avez selectionner aucun Ligne !", MsgBoxStyle.Critical, "Réssayer !")
            End If
        End With

    End Sub

    Private Sub List_tous_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles List_tous.SelectedIndexChanged
        vider()
        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(7).Text)
                    modif = CInt(.Items(.SelectedIndices(i)).SubItems(7).Text)
                Next
            End If
        End With

        Try
            Cmd = New SqlCommand("Select * From Personnels where Id_Personnels =" & n, Con)
            open()
            Dr = Cmd.ExecuteReader()
            Dr.Read()
            txt_nom.Text = Dr("Nom")
            txt_prenom.Text = Dr("Prenom")
            Comb_Type_Travail.Text = Dr("Categorie")
            txt_adresse.Text = Dr("Adresse")
            txt_mail.Text = Dr("Email")
            txt_cin.Text = Dr("Cin")
            txt_salair.Text = Dr("Salaire")
            Date_emploi.Text = Dr("Date_emploi")
            txt_remarq.Text = Dr("Remarque")
            comb_ville.Text = Dr("Ville")
            If Dr("Actif") = "Actif" Then
                Check_actif.Checked = True
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception

            'MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try

        'جلب الصورة
        Cmd = New SqlCommand("SELECT Photo FROM Personnels Where Id_Personnels =" & n, Con)
        Da = New SqlDataAdapter(Cmd)
        Ds = New DataSet()
        Da.Fill(Ds, "Personnels")
        Dim c As Integer = Ds.Tables("Personnels").Rows.Count
        If c > 0 Then
            Dim bytBLOBData() As Byte = _
                Ds.Tables("Personnels").Rows(c - 1)("Photo")
            Dim stmBLOBData As New MemoryStream(bytBLOBData)
            Pic_photo.Image = Image.FromStream(stmBLOBData)
        End If

        Try
            Cmd = New SqlCommand("Select Portable From Personnels_Tele Where Id_Personnels=" & n, Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                txt_portabl.Text = Dr("Portable")
            Loop
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub btn_modif_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_modif.Click
        If modif <> 0 Then

            If (txt_nom.Text <> "" And txt_prenom.Text <> "") Then
                If MsgBox("Vous Voulez Vraiment Modifier ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Modifier !") = MsgBoxResult.Yes Then
                    Try
                        Cmd = New SqlCommand("update Personnels set Nom=@nom,Prenom=@pren,Categorie=@categ,Adresse=@adres,Email=@email,Cin=@Cin,Actif=@Actif,Ville=@vill,Date_emploi=@Date_emploi,Salaire=@Salaire,Remarque=@remarq,Photo=@img" & _
                        " Where Id_Personnels=" & modif, Con)

                        Cmd.Parameters.Add("@nom", SqlDbType.VarChar).Value = txt_nom.Text
                        Cmd.Parameters.Add("@pren", SqlDbType.VarChar).Value = txt_prenom.Text
                        Cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = txt_cin.Text
                        Cmd.Parameters.Add("@categ", SqlDbType.VarChar).Value = Comb_Type_Travail.SelectedItem
                        Cmd.Parameters.Add("@adres", SqlDbType.VarChar).Value = txt_adresse.Text
                        Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = txt_mail.Text
                        If Check_actif.Checked = True Then
                            Cmd.Parameters.Add("@Actif", SqlDbType.VarChar).Value = "Actif"
                        Else
                            Cmd.Parameters.Add("@Actif", SqlDbType.VarChar).Value = ""
                        End If
                        Cmd.Parameters.Add("@vill", SqlDbType.VarChar).Value = comb_ville.SelectedItem
                        Cmd.Parameters.Add("@Date_emploi", SqlDbType.Date).Value = Date_emploi.Value.Date
                        Cmd.Parameters.Add("@Salaire", SqlDbType.Float).Value = CDbl(txt_salair.Text)
                        Cmd.Parameters.Add("@remarq", SqlDbType.Text).Value = txt_remarq.Text

                        'اضافة الصورة


                        Dim ms As MemoryStream = New MemoryStream()
                        Pic_photo.Image.Save(ms, ImageFormat.Jpeg)
                        Dim bytBLOBData(ms.Length - 1) As Byte
                        ms.Position = 0
                        ms.Read(bytBLOBData, 0, ms.Length)
                        Dim prm As New SqlParameter("@img", SqlDbType.VarBinary, _
                        bytBLOBData.Length, ParameterDirection.Input, False, _
                        0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
                        Cmd.Parameters.Add(prm)


                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()



                        Cmd = New SqlCommand("Update Personnels_Tele set Portable=@tele Where Id_Personnels=" & modif, Con)
                        Cmd.Parameters.Add("@tele", SqlDbType.VarChar).Value = txt_portabl.Text
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                        Afficher()
                        vider()
                        modif = 0
                        MsgBox("Modification a Réussi ", MsgBoxStyle.Information, "Réussi !")
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try


                End If
            Else
                MsgBox("Vous n'avez selectionner aucun Ligne !", MsgBoxStyle.Critical, "Réssayer !")
            End If
        End If
        modif = 0
    End Sub

    Private Sub btn_parcou_cv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        With OpenF
            .FileName = ""
            .Title = "Ovrir la Photo"
            .RestoreDirectory = True
            .InitialDirectory = "C:\"
            .Filter = "PDF Files (*.PDF)|*.PDF"
            .FilterIndex = 1
        End With
        If OpenF.ShowDialog = Windows.Forms.DialogResult.OK Then
            Pic_photo.Image = Image.FromFile(OpenF.FileName)

        End If
    End Sub

    Private Sub txt_portabl_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_portabl.TextChanged
        Dim selStart As Integer = txt_portabl.SelectionStart
        Dim selMoveLeft As Integer = 0
        Dim newStr As String = "" 'Build a new string by copying each valid character from the existing string. The new string starts as blank and valid characters are added 1 at a time.

        For i As Integer = 0 To txt_portabl.Text.Length - 1

            If "0123456789".IndexOf(txt_portabl.Text(i)) <> -1 Then 'Characters that are in the allowed set will be added to the new string.
                newStr = newStr & txt_portabl.Text(i)

            ElseIf i < selStart Then 'Characters that are not valid are removed - if these characters are before the cursor, we need to move the cursor left to account for their removal.
                selMoveLeft = selMoveLeft + 1

            End If
        Next

        txt_portabl.Text = newStr 'Place the new text into the textbox.
        txt_portabl.SelectionStart = selStart - selMoveLeft 'Move the cursor to the appropriate location.
    End Sub

    Private Sub txt_salair_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_salair.TextChanged
        If txt_salair.Text = "" Then
            txt_salair.Text = 0
        End If
    End Sub

    Private Sub txt_prenom_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_prenom.KeyPress
        If txt_prenom.Text.Length < 1 Then
            If Char.IsLower(e.KeyChar) Then
                'Convert to uppercase, and put at the caret position in the TextBox.
                txt_prenom.SelectedText = Char.ToUpper(e.KeyChar)
                e.Handled = True
            End If
        End If
    End Sub
End Class